CREATE TABLE AssetGroup
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name VARCHAR(200) NOT NULL
)
GO

CREATE TABLE MeterAssetGroup
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MeterID INT NOT NULL REFERENCES Meter(ID),
    AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID)
)
GO

CREATE NONCLUSTERED INDEX IX_MeterAssetGroup_MeterID
ON MeterAssetGroup(MeterID ASC)
GO

CREATE NONCLUSTERED INDEX IX_MeterAssetGroup_AssetGroupID
ON MeterAssetGroup(AssetGroupID ASC)
GO

CREATE TABLE LineAssetGroup
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    LineID INT NOT NULL REFERENCES Line(ID),
    AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID)
)
GO

CREATE NONCLUSTERED INDEX IX_LineAssetGroup_LineID
ON LineAssetGroup(LineID ASC)
GO

CREATE NONCLUSTERED INDEX IX_LineAssetGroup_AssetGroupID
ON LineAssetGroup(AssetGroupID ASC)
GO

CREATE TRIGGER Meter_AugmentAllAssetsGroup
ON Meter
AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;

    INSERT INTO MeterAssetGroup(MeterID, AssetGroupID)
    SELECT Meter.ID, AssetGroup.ID
    FROM inserted Meter CROSS JOIN AssetGroup
    WHERE AssetGroup.Name = 'AllAssets'
END
GO

CREATE TRIGGER Line_AugmentAllAssetsGroup
ON Line
AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;

    INSERT INTO LineAssetGroup(LineID, AssetGroupID)
    SELECT Line.ID, AssetGroup.ID
    FROM inserted Line CROSS JOIN AssetGroup
    WHERE AssetGroup.Name = 'AllAssets'
END
GO

ALTER TABLE UserAccount
ADD UNIQUE(Name)
GO

ALTER TABLE UserAccount
ADD PhoneConfirmed BIT NOT NULL DEFAULT 0
GO

ALTER TABLE UserAccount
ADD EmailConfirmed BIT NOT NULL DEFAULT 0
GO

ALTER TABLE UserAccount
ADD Approved BIT NOT NULL DEFAULT 0
GO

CREATE TABLE UserAccountAssetGroup
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    UserAccountID UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID),
    AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID),
    Dashboard BIT NOT NULL DEFAULT 1,
    Email BIT NOT NULL DEFAULT 0
)
GO

CREATE TRIGGER UserAccount_AugmentAllAssetsGroup
ON UserAccount
AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;

    INSERT INTO UserAccountAssetGroup(UserAccountID, AssetGroupID)
    SELECT UserAccount.ID, AssetGroup.ID
    FROM inserted UserAccount CROSS JOIN AssetGroup
    WHERE AssetGroup.Name = 'AllAssets'
END
GO

ALTER TABLE XSLTemplate
ADD UNIQUE(Name)
GO

ALTER TABLE EmailType
ADD SMS BIT NOT NULL DEFAULT 0
GO

CREATE TABLE EventEmailParameters
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EmailTypeID INT NOT NULL UNIQUE REFERENCES EmailType(ID),
    TriggersEmailSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT 0',
    EventDetailSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT '''' FOR XML PATH(''EventDetail''), TYPE',
    MinDelay FLOAT NOT NULL DEFAULT 10,
    MaxDelay FLOAT NOT NULL DEFAULT 60
)
GO

CREATE TABLE UserAccountEmailType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    UserAccountID UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID),
    EmailTypeID INT NOT NULL REFERENCES EmailType(ID)
)
GO

INSERT INTO AssetGroup(Name)
SELECT Name
FROM MeterGroup
GO

INSERT INTO AssetGroup(Name)
SELECT Name
FROM LineGroup
GO

INSERT INTO MeterAssetGroup(MeterID, AssetGroupID)
SELECT Meter.ID, AssetGroup.ID
FROM
    MeterMeterGroup JOIN
    Meter ON MeterMeterGroup.MeterID = Meter.ID JOIN
    MeterGroup ON MeterMeterGroup.MeterGroupID = MeterGroup.ID JOIN
    AssetGroup ON MeterGroup.Name = AssetGroup.Name
GO

INSERT INTO LineAssetGroup(LineID, AssetGroupID)
SELECT Line.ID, AssetGroup.ID
FROM
    LineLineGroup JOIN
    Line ON LineLineGroup.LineID = Line.ID JOIN
    LineGroup ON LineLineGroup.LineGroupID = LineGroup.ID JOIN
    AssetGroup ON LineGroup.Name = AssetGroup.Name
GO

INSERT INTO UserAccountAssetGroup(UserAccountID, AssetGroupID)
SELECT UserAccountID, AssetGroup.ID
FROM
    UserAccountMeterGroup JOIN
    MeterGroup ON UserAccountMeterGroup.MeterGroupID = MeterGroup.ID JOIN
    AssetGroup ON MeterGroup.Name = AssetGroup.Name
UNION
SELECT UserAccountID, AssetGroup.ID
FROM
    EmailGroupUserAccount JOIN
    EmailGroupMeterGroup ON EmailGroupUserAccount.EmailGroupID = EmailGroupMeterGroup.EmailGroupID JOIN
    MeterGroup ON EmailGroupMeterGroup.MeterGroupID = MeterGroup.ID JOIN
    AssetGroup ON MeterGroup.Name = AssetGroup.Name
UNION
SELECT UserAccountID, AssetGroup.ID
FROM
    EmailGroupUserAccount JOIN
    EmailGroupLineGroup ON EmailGroupUserAccount.EmailGroupID = EmailGroupLineGroup.EmailGroupID JOIN
    LineGroup ON EmailGroupLineGroup.LineGroupID = LineGroup.ID JOIN
    AssetGroup ON LineGroup.Name = AssetGroup.Name
GO

UPDATE UserAccountAssetGroup SET Email = 1
GO

INSERT INTO EventEmailParameters(EmailTypeID)
SELECT ID FROM EmailType
GO

INSERT INTO UserAccountEmailType(UserAccountID, EmailTypeID)
SELECT UserAccount.ID, EmailType.ID
FROM
    UserAccount JOIN
    EmailGroupUserAccount ON EmailGroupUserAccount.UserAccountID = UserAccount.ID JOIN
    EmailGroupType ON EmailGroupType.EmailGroupID = EmailGroupUserAccount.EmailGroupID JOIN
    EmailType ON EmailGroupType.EmailTypeID = EmailType.ID
GO

ALTER FUNCTION ComputeHash
(
    @eventID INT,
    @templateID INT
)
RETURNS BIGINT
BEGIN
    DECLARE @md5Hash BINARY(16)

    DECLARE @eventDetailSQL VARCHAR(MAX) =
    (
        SELECT EventDetailSQL
        FROM
            EmailType JOIN
            EventEmailParameters ON EventEmailParameters.EmailTypeID = EmailType.ID
        WHERE EmailType.XSLTemplateID = @templateID
    )

    DECLARE @eventDetail VARCHAR(MAX)
    EXEC sp_executesql @eventDetailSQL, @eventID, @eventDetail OUT

    SELECT @md5Hash = master.sys.fn_repl_hash_binary(CONVERT(VARBINARY(MAX), @eventDetail))
    FROM EventDetail
    WHERE EventID = @eventID

    SELECT @md5Hash = master.sys.fn_repl_hash_binary(@md5Hash + CONVERT(VARBINARY(MAX), Template))
    FROM XSLTemplate
    WHERE ID = @templateID

    RETURN CONVERT(BIGINT, SUBSTRING(@md5Hash, 0, 8)) ^ CONVERT(BIGINT, SUBSTRING(@md5Hash, 8, 8))
END
GO

CREATE VIEW MeterAssetGroupView
AS
SELECT
    MeterAssetGroup.ID,
    Meter.Name AS MeterName,
    Meter.ID AS MeterID,
    AssetGroupID,
    MeterLocation.Name AS Location
FROM
    MeterAssetGroup JOIN
    Meter ON MeterAssetGroup.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID
GO

CREATE VIEW LineAssetGroupView
AS
SELECT
    LineAssetGroup.ID,
    Line.AssetKey AS LineName,
    (SELECT TOP 1 LineName FROM MeterLine Where LineID = Line.ID) AS LongLineName,
    Line.ID AS LineID,
    AssetGroupID
FROM
    LineAssetGroup JOIN
    Line ON LineAssetGroup.LineID = Line.ID
GO

CREATE VIEW UserAccountAssetGroupView
AS
SELECT
    UserAccountAssetGroup.ID,
    UserAccountAssetGroup.UserAccountID,
    UserAccountAssetGroup.AssetGroupID,
    UserAccountAssetGroup.Dashboard,
    UserAccountAssetGroup.Email,
    UserAccount.Name AS Username,
    AssetGroup.Name AS GroupName
FROM
    UserAccountAssetGroup JOIN
    UserAccount ON UserAccountAssetGroup.UserAccountID = UserAccount.ID JOIN
    AssetGroup ON UserAccountAssetGroup.AssetGroupID = AssetGroup.ID
GO

ALTER VIEW UserMeter
AS
SELECT DISTINCT
    UserAccount.Name AS UserName,
    Meter.ID AS MeterID
FROM
    UserAccount JOIN
    UserAccountAssetGroup ON UserAccountAssetGroup.UserAccountID = UserAccount.ID LEFT OUTER JOIN
    MeterAssetGroup ON MeterAssetGroup.AssetGroupID = UserAccountAssetGroup.AssetGroupID LEFT OUTER JOIN
    LineAssetGroup ON LineAssetGroup.AssetGroupID = UserAccountAssetGroup.AssetGroupID LEFT OUTER JOIN
    MeterLine ON MeterLine.LineID = LineAssetGroup.LineID JOIN
    Meter ON
        MeterAssetGroup.MeterID = Meter.ID OR
        MeterLine.MeterID = Meter.ID
WHERE
    UserAccount.Approved <> 0 AND
    UserAccountAssetGroup.Dashboard <> 0
GO

UPDATE EventEmailParameters
SET TriggersEmailSQL = 'SELECT
    CASE WHEN EventType.Name = ''Fault''
        THEN 1
        ELSE 0
    END
FROM
    Event JOIN
    EventType ON Event.EventTypeID = EventType.ID
WHERE Event.ID = {0}'
GO

UPDATE EventEmailParameters
SET EventDetailSQL = 'DECLARE @timeTolerance FLOAT = (SELECT CAST(Value AS FLOAT) FROM Setting WHERE Name = ''TimeTolerance'')
DECLARE @lineID INT
DECLARE @startTime DATETIME2
DECLARE @endTime DATETIME2

SELECT
    @lineID = LineID,
    @startTime = dbo.AdjustDateTime2(StartTime, -@timeTolerance),
    @endTime = dbo.AdjustDateTime2(EndTime, @timeTolerance)
FROM Event
WHERE ID = 1902738

SELECT *
INTO #systemEvent
FROM Event
WHERE
    Event.EndTime >= @startTime AND
    Event.StartTime <= @endTime

SELECT *
INTO #lineEvent
FROM Event
WHERE
    Event.LineID = @lineID AND
    Event.EndTime >= @startTime AND
    Event.StartTime <= @endTime

SELECT
    ROW_NUMBER() OVER(PARTITION BY Event.MeterID ORDER BY FaultSummary.Inception) AS FaultNumber,
    FaultSummary.ID AS FaultSummaryID,
    Meter.AssetKey AS MeterKey,
    Meter.Make AS MeterMake,
    Meter.Name AS MeterName,
    MeterLocation.AssetKey as StationKey,
    MeterLocation.Name AS StationName,
    Line.AssetKey AS LineKey,
    MeterLine.LineName,
    MeterLine.BreakerName,
    FaultSummary.FaultType,
    FaultSummary.Inception,
    FaultSummary.DurationCycles,
    FaultSummary.DurationSeconds * 1000.0 AS DurationMilliseconds,
    FaultSummary.PrefaultCurrent,
    FaultSummary.PostfaultCurrent,
    FaultSummary.ReactanceRatio,
    FaultSummary.CurrentMagnitude AS FaultCurrent,
    FaultSummary.Algorithm,
    FaultSummary.Distance AS SingleEndedDistance,
    DoubleEndedFaultSummary.Distance AS DoubleEndedDistance,
    DoubleEndedFaultSummary.Angle AS DoubleEndedAngle,
    RIGHT(DataFile.FilePath, CHARINDEX(''\'', REVERSE(DataFile.FilePath)) - 1) AS FileName,
    FaultSummary.EventID,
    Event.StartTime AS EventStartTime,
    SimpleSummary.Distance AS Simple,
    ReactanceSummary.Distance AS Reactance,
    Event.EndTime AS EventEndTime
INTO #summaryData
FROM
    #lineEvent Event JOIN
    FaultSummary ON
        FaultSummary.EventID = Event.ID AND
        FaultSummary.IsSelectedAlgorithm <> 0 AND
        FaultSummary.IsSuppressed = 0 LEFT OUTER JOIN
    FaultSummary SimpleSummary ON
        FaultSummary.EventID = SimpleSummary.EventID AND
        FaultSummary.Inception = SimpleSummary.Inception AND
        SimpleSummary.Algorithm = ''Simple'' LEFT OUTER JOIN
    FaultSummary ReactanceSummary ON
        FaultSummary.EventID = ReactanceSummary.EventID AND
        FaultSummary.Inception = ReactanceSummary.Inception AND
        ReactanceSummary.Algorithm = ''Reactance'' JOIN
    DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Event.LineID JOIN
    Line ON Line.ID=MeterLine.LineID LEFT OUTER JOIN
    DoubleEndedFaultDistance ON DoubleEndedFaultDistance.LocalFaultSummaryID = FaultSummary.ID LEFT OUTER JOIN
    DoubleEndedFaultSummary ON DoubleEndedFaultSummary.ID = DoubleEndedFaultDistance.ID
WHERE
    DataFile.FilePath LIKE ''%.DAT'' OR
    DataFile.FilePath LIKE ''%.D00'' OR
    DataFile.FilePath LIKE ''%.PQD'' OR
    DataFile.FilePath LIKE ''%.RCD'' OR
    DataFile.FilePath LIKE ''%.RCL'' OR
    DataFile.FilePath LIKE ''%.SEL'' OR
    DataFile.FilePath LIKE ''%.EVE'' OR
    DataFile.FilePath LIKE ''%.CEV''

DECLARE @url VARCHAR(MAX) = (SELECT Value FROM DashSettings WHERE Name = ''System.URL'')

SELECT
    (
        SELECT ID AS [@id]
        FROM #lineEvent
        FOR XML PATH(''Event''), TYPE
    ) AS [Events],
    (
        SELECT ID AS [@id]
        FROM #systemEvent
        FOR XML PATH(''Event''), TYPE
    ) [SystemEvent],
    (
        SELECT
            FaultNumber AS [@num],
            (
                SELECT
                    MeterKey,
                    MeterName,
                    StationKey,
                    StationName,
                    LineKey,
                    LineName,
                    FaultType,
                    Inception,
                    DurationCycles,
                    DurationMilliseconds,
                    PrefaultCurrent,
                    PostfaultCurrent,
                    ReactanceRatio,
                    FaultCurrent,
                    Algorithm,
                    SingleEndedDistance,
                    DoubleEndedDistance,
                    DoubleEndedAngle,
                    EventStartTime,
                    EventEndTime,
                    FileName,
                    RIGHT(FileName, 8) AS ShortFileName,
                    BreakerName,
                    EventID,
                    FaultSummaryID AS FaultID,
                    CASE WHEN ABS(Reactance/COALESCE(Simple,1)) > 0.6 THEN ''LOW''
                            WHEN ABS(Reactance/COALESCE(Simple,1)) < 0.4 THEN ''HIGH''
                            ELSE ''MEDIUM''
                    END AS Ratio
                FROM #summaryData
                WHERE FaultNumber = Fault.FaultNumber
                FOR XML PATH(''SummaryData''), TYPE
            )
        FROM
        (
            SELECT DISTINCT FaultNumber
            FROM #summaryData
        ) Fault
        FOR XML PATH(''Fault''), TYPE
    ) AS [Faults],
    MeterLine.LineName AS [Line/Name],
    Line.AssetKey AS [Line/AssetKey],
    Line.Description AS [Line/Description],
    FORMAT(Line.Length, ''0.##########'') AS [Line/Length],
    FORMAT(SQRT(LineImpedance.R1 * LineImpedance.R1 + LineImpedance.X1 * LineImpedance.X1), ''0.##########'') AS [Line/Z1],
    CASE LineImpedance.R1 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2(LineImpedance.X1, LineImpedance.R1) * 180 / PI(), ''0.##########'') END AS [Line/A1],
    FORMAT(LineImpedance.R1, ''0.##########'') AS [Line/R1],
    FORMAT(LineImpedance.X1, ''0.##########'') AS [Line/X1],
    FORMAT(SQRT(LineImpedance.R0 * LineImpedance.R0 + LineImpedance.X0 * LineImpedance.X0), ''0.##########'') AS [Line/Z0],
    CASE LineImpedance.R0 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2(LineImpedance.X0, LineImpedance.R0) * 180 / PI(), ''0.##########'') END AS [Line/A0],
    FORMAT(LineImpedance.R0, ''0.##########'') AS [Line/R0],
    FORMAT(LineImpedance.X0, ''0.##########'') AS [Line/X0],
    FORMAT(SQRT(POWER((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, 2) + POWER((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, 2)), ''0.##########'') AS [Line/ZS],
    CASE 2.0 * LineImpedance.R1 + LineImpedance.R0 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, (2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0) * 180 / PI(), ''0.##########'') END AS [Line/AS],
    FORMAT((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, ''0.##########'') AS [Line/RS],
    FORMAT((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, ''0.##########'') AS [Line/XS],
    @url AS [PQDashboard]
FROM
    Event JOIN
    Line ON Event.LineID = Line.ID JOIN
    MeterLine ON
        MeterLine.MeterID = Event.MeterID AND
        MeterLine.LineID = Event.LineID JOIN
    LineImpedance ON LineImpedance.LineID = Line.ID
WHERE Event.ID = 1902738
FOR XML PATH(''EventDetail''), TYPE'
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Email.ApprovalAddress', 'xda-admin@gridprotectionalliance.org', 'xda-admin@gridprotectionalliance.org')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('EventEmail.Enabled', 'False', 'False')
GO

INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('System.URL', 'http://localhost/PQDashboard', 1)
GO